![JSONL ファイルをもとに Amazon Athena で CREATE TABLE してみる](https://devio2023-media.developers.io/wp-content/uploads/2023/08/amazon-athena.png)
JSONL ファイルをもとに Amazon Athena で CREATE TABLE してみる
こんにちは!よしななです。
今回は、JSONL データを Amazon Athena でCREATE TABLE
する機会があったので備忘録としてブログに残します。
目次
- やりたいこと
- 対象読者
- 事前準備
- AWS へのサインイン
- データを Amazon S3 に配置する
- JSONL ファイルのアップロード
- 手順
- Amazon Athena で JSONL を操作してみる
- CREATE TABLE の実行
- テーブルの検索
- Amazon Athena で JSONL を操作してみる
やりたいこと
- Amazon S3 にアップロードされた 以下の JSONL ファイルをもとに、Amazon Athena 上で
CREATE TABLE
を実行します。 - 作成したテーブルに対し、
SELECT
文を使用してGrades
カラムをJapanese(国語)、Math(数学)、English(英語)…と分けて出力するところまでを実施します。 -
今回対象となるデータは以下となります。
{"No": 1, "Name": "山田太郎", "Gender": "Male", "Grades": {"Japanese": 82, "Math": 74, "English": 88, "Science": 90, "SocialStudies": 85}} {"No": 2, "Name": "佐藤花子", "Gender": "Female", "Grades": {"Japanese": 90, "Math": 68, "English": 95, "Science": 87, "SocialStudies": 92}} {"No": 3, "Name": "鈴木一郎", "Gender": "Male", "Grades": {"Japanese": 75, "Math": 80, "English": 78, "Science": 85, "SocialStudies": 80}} {"No": 4, "Name": "高橋愛", "Gender": "Female", "Grades": {"Japanese": 88, "Math": 92, "English": 94, "Science": 90, "SocialStudies": 91}}
- 今回対象となるデータの型は以下の通りです。
カラム名 | データ型 |
---|---|
No | int |
Name | string |
Gender | string |
Grades | struct |
対象読者
- JSONL ファイルをもとに Amazon Athena 上で
CREATE TABLE
したい方
事前準備
AWS マネジメントコンソールへのサインイン
AWS マネジメントコンソールからサインインが必要です。
以下の公式ドキュメントを参考に、サインインを行います。
https://docs.aws.amazon.com/ja_jp/signin/latest/userguide/how-to-sign-in.html
データを Amazon S3 に配置する
まず、JSONL ファイルをアップロードするための S3 バケットを作成します。
ホーム画面上部 → 検索コンソールから S3 を検索 → バケットを作成 をクリックします。
バケット作成画面に遷移するので、ここから S3 バケットを作成します。
設定項目
AWS リージョン:アジアパシフィック (東京) ap-northeast-1 を選択します。
バケット名:任意の名前を入力します。今回はjsonl-test
に設定します。
その他の設定はデフォルトの状態にします。
JSONL ファイルのアップロード
作成した S3 バケットに、テーブルのデータソースとなる JSONL ファイルのアップロードを行います。作成したバケットを選択 → アップロードをクリックします。
以下の画面に遷移します。
ファイルのアップロードをクリックするとエクスプローラーが開くので、該当のファイルを選択してアップロードします。こちらでファイルのアップロードが完了となります。
CREATE DATABASE の実行
次に、Amazon Athena 上でCREATE DATABASE
を実行します。
ホーム画面上部 → 検索コンソールから Amazon Athena を検索 → クエリエディタ をクリックします。
クエリエディタを開いたら、以下を実行し DATABASE を作成します。
CREATE DATABASE jsonl_test;
手順
CREATE TABLE の実行
データベースが作成されたので、CREATE TABLE
を実行してみます。
CREATE EXTERNAL TABLE IF NOT EXISTS `jsonl_test_table`( `No` int, `Name` string, `Gender` string, `Grades` struct <`Japanese`:int,`Math`:int,`English`:int,`Science`:int,`SocialStudies`:int> ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://jsonl-test/';
CREATE TABLE クエリの構文について
上記のクエリの構文について、以下で解説します。
下記以外にもCREATE TABLE
のオプションを指定できますが、JSONL ファイルの読み込みには下記の3項目が設定されていればクエリの実行が可能です。
クエリの作成には、以下の公式ドキュメントページを参考にしています。
https://docs.aws.amazon.com/athena/latest/ug/create-table.html
1.CREATE TABLE
- EXTERNAL
- Amazon Athena 上で
CREATE TABLE
する場合、Iceberg テーブルを作成する場合以外でEXTERNAL
が抜けているとエラーになります。
- Amazon Athena 上で
- IF NOT EXISTS
- 同じ名前のテーブルが既にデータベースに存在する場合、
CREATE TABLE
をスキップします。
- 同じ名前のテーブルが既にデータベースに存在する場合、
2. ROW FORMAT SERDE
- 作成するテーブルのデータ形式を指定します。
- テーブル作成元のデータ形式と
ROW FORMAT SERDE
の形式が一致していないとエラーになります。 - 今回は、JSONL ファイルを読み込む必要があるので
'org.openx.data.jsonserde.JsonSerDe'
を指定します。
3. LOCATION
- データが配置されているバケット名を入力します。
- 今回は、
jsonl-test
直下にtest_jsonl.jsonl
を配置したのでs3://jsonl-test/
を入力します。
オブジェクト型について
オブジェクト型のデータを CREATE TABLE
するには、struct<col_name:data_type,...>
と記述します。
出力結果
テーブルが作成されたので、SELECT * FROM "jsonl_test"."jsonl_test_table" limit 10;
を実行しテーブルの中身を確認してみます。
問題なくテーブルが出力されました!
テーブルの検索
次に、SELECT
文を使用してGrades
カラムをJapanese(国語)、Math(数学)、English(英語)…と分けて出力してみたいと思います。
SELECT "No", "Name", "Gender", "Grades"."Japanese" as "国語", "Grades"."Math" as "数学", "Grades"."English" as "英語" FROM "jsonl_test"."jsonl_test_table" limit 10;
SELECT FROM クエリの構文について
1. Grades カラムの展開
Grades
カラムを Japanese(国語)、Math(数学)、English(英語)… と分割して出力するには、{カラム名}.{オブジェクトの項目名}
で出力が可能です。
出力結果
上記のクエリを実行すると、以下の通りGrades
カラムをJapanese(国語)、Math(数学)、English(英語)と分けて出力することができました!
以上で、JSONL ファイルをもとに Amazon Athena でCREATE TABLE
してみる は完了となります。ここまで読んでいただきありがとうございました!